package Laborum2.repository;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import Laborum2.Basicas.*;

@Component
public class JdbcPostOferRepository implements PosOferRepository{
	List<PostOfer> pof = new ArrayList <PostOfer>();
	PostOfer po = new PostOfer();
	TempCurri tc =new TempCurri();
	
	@Autowired
	private DataSource dataSource;
	
	private void closeConecction(ResultSet rs, PreparedStatement ps,
			Connection conn) {

		if (rs != null) {
			try {
				// Close to prevent database cursor exhaustion
				rs.close();
			} catch (SQLException ex) {
			}
		}
		if (ps != null) {
			try {
				// Close to prevent database cursor exhaustion
				ps.close();
			} catch (SQLException ex) {
			}
		}
		if (conn != null) {
			try {
				// Close to prevent database connection exhaustion
				conn.close();
			} catch (SQLException ex) {
			}
		}

	}

	private void closeConecction1(PreparedStatement ps, Connection conn) {

		if (ps != null) {
			try {
				// Close to prevent database cursor exhaustion
				ps.close();
			} catch (SQLException ex) {
			}
		}
		if (conn != null) {
			try {
				// Close to prevent database connection exhaustion
				conn.close();
			} catch (SQLException ex) {
			}
		}

	}


	@Override
	public List<PostOfer> obtenerPostOfer(int idOferta) {
		 String sql = "SELECT * from postXOfer where idOferta=? and estado =1" ;
			
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			try {
				conn = dataSource.getConnection();
				ps = conn.prepareStatement(sql);
				ps.setInt(1, idOferta);
			
				rs = ps.executeQuery();

				while (rs.next()) {
					po = new PostOfer();
					po.setIdOferta(rs.getInt("idOferta"));
					po.setDni(rs.getInt("dni"));	
					po.setEstado(rs.getInt("estado"));

					pof.add(po);
	              
			}
	           
			} catch (SQLException ex) {

			}
			closeConecction(rs, ps, conn);
	
	return pof;
	}


	@Override
	public List<PostOfer> obtenerPosOfer(int dni,int ofer) {
		
		 String sql = "SELECT * from postXOfer where dni=? and idOferta=? and estado =1" ;
			
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			try {
				conn = dataSource.getConnection();
				ps = conn.prepareStatement(sql);
				ps.setInt(1, dni);
				ps.setInt(2, ofer);
				rs = ps.executeQuery();

				while (rs.next()) {
					po = new PostOfer();
					po.setIdOferta(rs.getInt("idOferta"));
					po.setDni(rs.getInt("dni"));	
					po.setEstado(rs.getInt("estado"));

					pof.add(po);
	              
			}
	           
			} catch (SQLException ex) {

			}
			closeConecction(rs, ps, conn);
	
	return pof;
	}


	@Override
	public void ingresar(PostOfer pof) {
		//id,idOferta,dni,estado 
		String sql = "insert into postXOfer (idOferta,dni,estado) " +
        "values (?,?,?)";

       Connection conn = null;
       PreparedStatement ps = null;

        try {
           conn = dataSource.getConnection();
           ps = conn.prepareStatement(sql);

           ps.setInt(1, pof.getIdOferta());
           ps.setInt(2, pof.getDni());
           ps.setInt(3, pof.getEstado());
           ps.execute();

       } catch (SQLException ex) {
        }
      
            closeConecction1(ps, conn);

	}


	@Override
	public void eliminar(int codigo) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public List<TempCurri> busquedacv(int dni) {
		// TODO Auto-generated method stub
		TempCurri t =new TempCurri();
		List <TempCurri> temp= new ArrayList <TempCurri>();
		String sql = "SELECT p.dni as dni, concat(p.nombre,' ' ,p.paterno,' ', p.materno) as nombre, p.dire as direccion, p.celular as cel, p.correo as mail," +
				"l.empresa as emp, c.desCargo as carg, l.descripcion as descrip," +
				"l.referencia as refer,e.desEst as estudios, d.tipo as espec" +
				"FROM postulantes p INNER JOIN Laboral l ON p.dni = l.dni " +
				"INNER JOIN DetalleEstudios d ON p.dni = d.dni" +
				"INNER JOIN estudios e ON d.id=e.id " +
				"INNER JOIN Cargos c ON l.cargo=c.id AND p.dni=?" ;

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
               try {
            	   conn = dataSource.getConnection();
       			ps = conn.prepareStatement(sql);
    			ps.setInt(1, dni);
    		
    			rs = ps.executeQuery();

    			while (rs.next()) {
    				t = new TempCurri();
    				
    			   t.setDni(rs.getInt("dni"));	
    			   t.setPos(rs.getString("nombre"));
    			   t.setDire(rs.getString("direccion"));
    			   t.setCelular(rs.getInt("cel"));
    			   t.setCorreo(rs.getString("mail"));
    			   t.setEmpresa(rs.getString("emp"));
    			   t.setCargo(rs.getString("carg"));
    		       t.setEstudios(rs.getString("estudios"));
                   t.setD1(rs.getInt("dif"));
                   t.setRefer(rs.getString("refer"));
                   t.setEstudios(rs.getString("estudios"));
                   t.setD2(rs.getInt("dif2"));
                   t.setTipo(rs.getString("espec"));
               	System.out.println("programa "+ t.getD2()+ " "+ t.getPos());		
    				temp.add(t);
    			for(int i=0;i<temp.size();i++){
    				System.out.println("programa1 "+ temp.get(i).getD2()+ " "+ temp.get(i).getPos());
    			}
    						
    			}
            	   
            	   
            	   
               }catch (SQLException ex) {

   			}
   			closeConecction(rs, ps, conn);

		return temp;
	 
 

	}

	

	
	
	
}